>> How should I do this?
>
> Can't at the moment.
ups, OK -- then I misunderstand something ;)
>> or, in more detail the exact function:
>>
>>
>> CREATE FUNCTION get_emotions (timestamp) RETURNS SETOF records AS
>> '
>> DECLARE
>> start ALIAS FOR $1;
>> end_id int4;
>> BEGIN
>> SELECT emotion_id FROM emotions
>> WHERE date <= start
>> LIMIT 1
>> INTO end_id;
>
> Not entirely clear what your function is for, but the above select looks a
> bit odd. Do you not want to "order by" here so you can get the "most
> recent" emotion_id or whatever?
In detail, I want 300 rows older then a specific date (timeslider), but
they are sorted by time AND an additional rating. For this i have to sort
the hole table without index -- but if i presort the 3000 rows before the
specific date and catch the 300 best rated/timed rows, i save lot of time.
It's not critical if there are some faulty rows selected ...
This is the only reason to select a subpart (3000 Rows) of the table bevore
doing the final selection which rows should be taken.
> I'd rewrite this as just a select, or a view if you want to keep things
> clean in the application, possibly with that first select encapsulated in
> a function (sorry, I'm not entirely clear what your code is doing).
>
> so:
>
> CREATE VIEW get_emotions_view AS
> SELECT emotion_id, emotion1, ...
> ORDER BY date_epoch + full_rating*3600*12
> LIMIT 300;
hmmm, but with this, the hole ORDER BY goes throug the hole table (might be
a lot of rows), with not using the index.
For now i do the hole stuff on client side with two selects:
First selecting the end_id, then (2. Statement) sort the stuff within
end_id and end_id-3000 and return the 300 most "best".
my $end_id = $self->db_h->selectrow_array( "SELECT emotion_id FROM emotions WHERE date <= ?
ORDER BY date DESC LIMIT 1", undef, $self->date_from_sliderpos($params[0]));
my $st_h = $self->db_h->prepare( " SELECT emotion_id, emotion1, ..., full_rating, date
FROMemotions WHERE emotion_id BETWEEN ? AND ? ORDER BY date_epoch +
full_rating*(3600*12) LIMIT 300 ");
$st_h->execute($end_id-3000, $end_id) or die "execute kaputt";
$st_h->bind_columns(...);
[...]
Thanks and Ciao
Alvar
--
| AGI ............................................................... |
| Magirusstrasse 21B, 70469 Stuttgart . Fon +49 (0)711.228 74-50 .... |
| http://www.agi.com/diary/ (english) . http://www.agi.de/tagebuch/ . |
| >>>>>> NEWS >>> AGI holt Bronze-Loewen in Cannes! <<<<<<<<<<<<<<<<< |